
package com.be.beadmin.product.repository;

import com.be.beadmin.product.entity.PlantCapacityCalendar;
import com.be.beadmin.project.entity.Info;
import feign.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
* @author zjm
* @date 2023-03-26
**/
public interface PlantCapacityCalendarRepository extends JpaRepository<PlantCapacityCalendar, String>, JpaSpecificationExecutor<PlantCapacityCalendar> {

    PlantCapacityCalendar findPlantCapacityCalendarByIdAndDelete(String id, Integer delete);

    @Query(value = "select distinct pcc.batch_plan_id from t_om_plant_capacity_calendar pcc where pcc.is_delete = ?1",nativeQuery = true)
    List<String> findBatchPlanIdsByDelete(Integer delete);

    @Query(value = "select distinct pcc.process_id from t_om_plant_capacity_calendar pcc where pcc.is_delete = ?1",nativeQuery = true)
    List<String> findProcessIdsByDelete(Integer delete);

    @Query(value = "select distinct pcc.date from t_om_plant_capacity_calendar pcc where pcc.is_delete = ?1",nativeQuery = true)
    List<Date> findDatesByDelete(Integer delete);


    @Modifying
    @Transactional
    @Query(value = "delete from t_om_plant_capacity_calendar pcc where pcc.batch_product_id = ?1 and pcc.process_id = ?2 and pcc.is_delete = ?3",nativeQuery = true)
    void deleteByBatchProductIdAndProcessIdAndDelete(String batchProductId, String processId, int delete);

    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.real_work_hour = 0  where pcc.batch_product_id = ?1 and pcc.process_id = ?2 ",nativeQuery = true)
    void updateRealCapByBatchProductIdAndProcessId(String batchProductId, String processI);

    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.real_work_hour = 0  where pcc.batch_product_id = ?1 and pcc.process_id = ?2 and pcc.date = ?3",nativeQuery = true)
    void updateRealCapByBatchProductIdAndProcessIdAndDate(String batchProductId, String processI,Date date);



    void deletePlantCapacityCalendarsByBatchProductIdAndProcessIdAndDate(String batchProductId, String processI,Date date);


    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.work_hour = 0  where pcc.batch_product_id = ?1 and pcc.process_id = ?2 ",nativeQuery = true)
    void updatePlanCapByBatchProductIdAndProcessId(String batchProductId, String processI);


    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.work_hour = 0  where pcc.batch_plan_id = ?1 and pcc.process_id = ?2 ",nativeQuery = true)
    void updatePlanCapByBatchPlanIdAndProcessId(String batchPlanId, String processI);


    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.work_hour = 0  where pcc.batch_plan_id = ?1",nativeQuery = true)
    void clearPlanCapByBatchPlanId(String batchPlanId);


    @Modifying
    @Transactional
    @Query(value = "update t_om_plant_capacity_calendar pcc set pcc.real_work_hour = 0  where pcc.batch_plan_id = ?1",nativeQuery = true)
    void clearRealPlanCapByBatchPlanId(String batchPlanId);

    PlantCapacityCalendar findPlantCapacityCalendarById(String id);


    //new com.be.beadmin.product.dto.output.infoDto(i.projectCode,i.projectName)
    @Query(value = "SELECT\n" +
            "i  \n" +
            "FROM\n" +
            "Info i \n" +
            "WHERE\n" +
            "i.id in (\n" +
            "SELECT\n" +
            "mp.infoId \n" +
            "FROM\n" +
            "MasterPlan mp \n" +
            "WHERE\n" +
            "mp.id in (\n" +
            "SELECT\n" +
            "plan.masterPlanId \n" +
            "FROM\n" +
            "BatchPlan plan \n" +
            "WHERE\n" +
            "plan.id in ( SELECT pcc.batchPlanId FROM PlantCapacityCalendar pcc WHERE pcc.id = ?1 AND pcc.delete = 0 ) \n" +
            "AND plan.delete = 0 \n" +
            ") \n" +
            "AND mp.delete = 0 \n" +
            ") \n" +
            "AND i.delete = 0")
    Info findInfoByPlantCapacityCalendarId(String id);


    @Query(value = "select sum(pcc.work_hour) from t_om_plant_capacity_calendar pcc where DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?1, '%Y-%m-%d') and pcc.is_delete = ?2",nativeQuery = true)
    Float sumWorkHourByDate(Date date, int delete);

    @Query(value = "select sum(pcc.real_work_hour) from t_om_plant_capacity_calendar pcc where DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?1, '%Y-%m-%d') and pcc.is_delete = ?2",nativeQuery = true)
    Float sumRealWorkHourByDate(Date date, int delete);

    @Query(value = "select sum(pcc.work_hour) from t_om_plant_capacity_calendar pcc where DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?1, '%Y-%m-%d') and pcc.process_id = ?2 and pcc.is_delete = ?3",nativeQuery = true)
    Float sumWorkHourByDateAndProcess(Date date, String processId, int delete);

    @Query(value = "select sum(pcc.real_work_hour) from t_om_plant_capacity_calendar pcc where DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?1, '%Y-%m-%d') and pcc.process_id = ?2 and pcc.is_delete = ?3",nativeQuery = true)
    Float sumRealWorkHourByDateAndProcess(Date date, String processId, int delete);


    @Query(value = "select sum(pcc.real_work_hour) from t_om_plant_capacity_calendar pcc where pcc.batch_plan_id = ?1 and DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?2, '%Y-%m-%d') and pcc.process_id = ?3 and pcc.is_delete = ?4",nativeQuery = true)
    Float sumRealWorkHourByBatchAndDateAndProcess(String batchPlanId ,Date date, String processId, int delete);

    @Query(value = "select sum(pcc.work_hour) from t_om_plant_capacity_calendar pcc where pcc.batch_plan_id = ?1 and DATE_FORMAT(pcc.date, '%Y-%m-%d') = DATE_FORMAT(?2, '%Y-%m-%d') and pcc.process_id = ?3 and pcc.is_delete = ?4",nativeQuery = true)
    Float sumWorkHourByBatchAndDateAndProcess(String batchPlanId ,Date date, String processId, int delete);


    @Query(value = "SELECT DATE_FORMAT(d.dateField, '%Y-%m-%d'), COALESCE(SUM(e.work_hour), 0) AS sumNumber " +
            "FROM (SELECT DATE_ADD(:startDate, INTERVAL seq DAY) AS dateField " +
            "      FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 " +
            "            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 " +
            "            UNION ALL SELECT 8 UNION ALL SELECT 9) AS tmp " +
            "      WHERE DATE_ADD(:startDate, INTERVAL seq DAY) <= :endDate) AS d " +
            "LEFT JOIN t_om_plant_capacity_calendar e ON d.dateField = e.date AND e.is_delete = :delete AND e.process_id = :processId " +
            "GROUP BY d.dateField order by d.dateField",nativeQuery = true)
    List<Object[]> sumWorkHourByDateAndRangeAndProcess(@Param("startDate") Date startDate, @Param("endDate") Date endDate,@Param("processId") String processId, @Param("delete") int delete);

    @Query(value = "SELECT DATE_FORMAT(d.dateField, '%Y-%m-%d'), COALESCE(SUM(e.real_work_hour), 0) AS sumNumber " +
            "FROM (SELECT DATE_ADD(:startDate, INTERVAL seq DAY) AS dateField " +
            "      FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 " +
            "            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 " +
            "            UNION ALL SELECT 8 UNION ALL SELECT 9) AS tmp " +
            "      WHERE DATE_ADD(:startDate, INTERVAL seq DAY) <= :endDate) AS d " +
            "LEFT JOIN t_om_plant_capacity_calendar e ON d.dateField = e.date AND e.is_delete = :delete AND e.process_id = :processId " +
            "GROUP BY d.dateField order by d.dateField",nativeQuery = true)
    List<Object[]> sumRealWorkHourByDateAndRangeAndProcess(@Param("startDate") Date startDate, @Param("endDate") Date endDate,@Param("processId") String processId, @Param("delete") int delete);



    @Query(value = "SELECT DATE_FORMAT(d.dateField, '%Y-%m-%d'), COALESCE(SUM(e.work_hour), 0) AS sumNumber " +
            "FROM (SELECT DATE_ADD(:startDate, INTERVAL seq DAY) AS dateField " +
            "      FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 " +
            "            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 " +
            "            UNION ALL SELECT 8 UNION ALL SELECT 9) AS tmp " +
            "      WHERE DATE_ADD(:startDate, INTERVAL seq DAY) <= :endDate) AS d " +
            "LEFT JOIN t_om_plant_capacity_calendar e ON d.dateField = e.date AND e.is_delete = :delete " +
            "GROUP BY d.dateField order by d.dateField",nativeQuery = true)
    List<Object[]> sumWorkHourByDateAndRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("delete") int delete);

    @Query(value = "SELECT DATE_FORMAT(d.dateField, '%Y-%m-%d'), COALESCE(SUM(e.real_work_hour), 0) AS sumNumber " +
            "FROM (SELECT DATE_ADD(:startDate, INTERVAL seq DAY) AS dateField " +
            "      FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 " +
            "            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 " +
            "            UNION ALL SELECT 8 UNION ALL SELECT 9) AS tmp " +
            "      WHERE DATE_ADD(:startDate, INTERVAL seq DAY) <= :endDate) AS d " +
            "LEFT JOIN t_om_plant_capacity_calendar e ON d.dateField = e.date AND e.is_delete = :delete " +
            "GROUP BY d.dateField order by d.dateField",nativeQuery = true)
    List<Object[]> sumRealWorkHourByDateAndRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("delete") int delete);

    /**
     SELECT dates.date_field, COALESCE(SUM(pcc.work_hour), 0) AS sum_number
     FROM (
         SELECT generate_series(?1::date, ?2::date, '1 day') AS date_field
     ) AS dates
     LEFT JOIN t_om_plant_capacity_calendar AS pcc ON dates.date_field = pcc.date and pcc.process_id = ?3 and pcc.is_delete = ?4
     GROUP BY dates.date_field;
     */


    PlantCapacityCalendar findPlantCapacityCalendarByBatchProductIdAndDateAndProcessIdAndDelete(String batchProductId,Date date, String processId, Integer delete);

    PlantCapacityCalendar findPlantCapacityCalendarByBatchProductIdAndDateAndProcessIdAndUserIdAndDelete(String batchProductId,Date date, String processId, String userId, Integer delete);

    List<PlantCapacityCalendar> findPlantCapacityCalendarsByBatchPlanIdAndDateAndProcessIdAndDelete(String batchPlanId,Date date, String processId, Integer delete);


    PlantCapacityCalendar findPlantCapacityCalendarByBatchPlanIdAndDateAndProcessIdAndDelete(String batchPlanId,Date date, String processId, Integer delete);
    PlantCapacityCalendar findPlantCapacityCalendarByBatchPlanIdAndDateAndProcessIdAndUserIdAndDelete(String batchPlanId,Date date, String processId,String userId, Integer delete);

    @Modifying
    @Transactional
    @Query(value = "select sum(pcc.) from t_om_plant_capacity_calendar pcc where pcc.batch_product_id = ?1 and pcc.process_id = ?2 and pcc.date = ?3",nativeQuery = true)
    float sumCapSumByBatchPlan(List<String> batchProductIds , String processId , Date date);


    @Query(value = "select sum(pcc.work_hour) from t_om_plant_capacity_calendar pcc where pcc.batch_plan_id = ?1 and pcc.is_delete = ?2",nativeQuery = true)
    Float sumCapSumByBatchPlan(String batchPlanId,int delete);


    @Query(value = "select sum(pcc.real_work_hour) from t_om_plant_capacity_calendar pcc where pcc.batch_plan_id = ?1 and pcc.is_delete = ?2",nativeQuery = true)
    Float sumRealCapSumByBatchPlan(String batchPlanId,int delete);

    List<PlantCapacityCalendar> findPlantCapacityCalendarByBatchProductCodeAndProcessCodeAndDelete(String batchProductCode, String processCode, int delete);

    void deletePlantCapacityCalendarsByBatchPlanId(String id);

    List<PlantCapacityCalendar> findPlantCapacityCalendarByBatchPlanIdAndProcessCodeAndDeleteAndDateBetween(String batchPlanId, String processCode, int i, Date startDate, Date endDate);

    List<PlantCapacityCalendar> findPlantCapacityCalendarByBatchProductCodeAndDelete(String productCode, Integer delete);
}